package com.css.fxfzfxqh.modules.seismicHazardData.repository.impl;

import cn.hutool.core.util.StrUtil;
import com.css.fxfzfxqh.modules.buildingIndividual.entity.BuildingIndividualEntity;
import com.css.fxfzfxqh.modules.seismicHazardData.constants.TableNameConstants;
import com.css.fxfzfxqh.modules.seismicHazardData.entity.HazardDataRecordEntity;
import com.css.fxfzfxqh.modules.seismicHazardData.entity.PsHazardDataEntity;
import com.css.fxfzfxqh.modules.seismicHazardData.entity.PsHazardDataValueEntity;
import com.css.fxfzfxqh.modules.seismicHazardData.param.psHazard.PageParam;
import com.css.fxfzfxqh.modules.seismicHazardData.param.psHazard.RecordPageParam;
import com.css.fxfzfxqh.modules.seismicHazardData.repository.PsHazardRepository;
import com.css.fxfzfxqh.modules.seismicHazardData.vo.HazardDataPageVo;
import com.css.fxfzfxqh.modules.seismicHazardData.vo.RecordsVo;
import com.css.fxfzfxqh.util.PlatformDateUtils;
import com.css.fxfzfxqh.util.PlatformObjectUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class PsHazardRepositoryImpl implements PsHazardRepository {

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;


    @Override
    public void savePsHazardData(PsHazardDataEntity entity,String tableName) {
        String sql = "insert into "+tableName+" values(:id,:kilometerGridId,:longitude,:latitude,:createUser,:createTime,:updateUser,:updateTime,:delFlag,ST_GeometryFromText(:geom,4490),:province,:city,:county,:year,:fileId,:type)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void savePsHazardDataValue(PsHazardDataValueEntity entity,String tableName) {
        jdbcTemplate.update("insert into "+tableName+"  (id,ph_id,im_value,f_im_value,type) values (?,?,?,?,? )",
                entity.getId(), entity.getPhId(), entity.getImValue(), entity.getFImValue(),entity.getType());
    }

    @Override
    public PsHazardDataEntity getPsHazardDataById(String id,String tableName) {
        try {
            String sql = "select id,kilometer_grid_id,longitude,latitude,file_id,type from "+tableName+" where id = '" + id+"'";
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(PsHazardDataEntity.class));
        } catch (DataAccessException e) {
            return null;
        }
    }

    @Override
    public List<PsHazardDataValueEntity> getPsHazardDataValueByPhId(String id,String type,String tableName) {
        String sql = "select * from "+tableName+" where ph_id = '"+id+"' and type = '"+type+"'";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(PsHazardDataValueEntity.class));
    }

    @Override
    public List<String> getAllKilometerGridId(String tableName) {
        String sql = "select id from "+tableName;
        List<String> counts = jdbcTemplate.query(sql, new RowMapper<String>() {
            @Override
            public String mapRow(ResultSet rs, int rowNum) throws SQLException {
                return rs.getString(1);
            }
        });
        return counts;
    }

    @Override
    public void deleteByKilometerGridId(String ids,String tableName) {
        String sql = "delete from "+tableName+" where id in ("+ids+")";
        jdbcTemplate.update(sql);
    }

    @Override
    public void deleteValueByPhIdAndType(String ids, String type,String tableName) {
        String sql = "delete from "+tableName+" where ph_id in ("+ids+") and type = '"+type+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public List<PsHazardDataEntity> getAllPhIdByKilometerGridId(String id,String tableName) {
        String sql = "select id,kilometer_grid_id,create_user,create_time from "+tableName+" where kilometer_grid_id in ("+id+")";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(PsHazardDataEntity.class));
    }

    @Override
    public Map<String, Object> page(PageParam param, String mainTableName) {
        StringBuilder sql = new StringBuilder("select * from "+mainTableName+" where del_flag = '0' and type like '%"+param.getType()+"%'");
        if(PlatformObjectUtils.isNotEmpty(param.getProvince())){
            sql.append(" and province = '"+param.getProvince()+"'");
        }
        if(PlatformObjectUtils.isNotEmpty(param.getCity())){
            sql.append(" and city = '"+param.getCity()+"'");
        }
        if(PlatformObjectUtils.isNotEmpty(param.getCounty())){
            sql.append(" and county = '"+param.getCounty()+"'");
        }
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        int firstIndex = (param.getPage() - 1) * param.getSize();
        int lastIndex = param.getSize();
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        sql.append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(HazardDataPageVo.class)).size();
        List<HazardDataPageVo> hazardDataPageVoList = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(HazardDataPageVo.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", hazardDataPageVoList);
        return map;
    }

    @Override
    public void update(PsHazardDataEntity it, String mainTableName) {
        jdbcTemplate.update("update  "+mainTableName+"  set longitude=?,latitude=?,update_user=?,update_time=?,province=?,city=?,county=?,file_id=?,type=?"
                        +" where id=?",
                it.getLongitude(),it.getLatitude(),it.getUpdateUser(),it.getUpdateTime(),it.getProvince(),it.getCity(),it.getCounty(),it.getFileId(),it.getType(),
                it.getId()
        );
    }

    @Override
    public void saveRecord(HazardDataRecordEntity entity) {
        jdbcTemplate.update("insert into fxqh_ps_hazard_data_record  (id,file_ids,create_user,create_time,region,data_range,create_user_name,type ) values (?,?,?,?,?,?,?,? )",
                entity.getId(),entity.getFileIds(),entity.getCreateUser(),entity.getCreateTime(),entity.getRegion(),entity.getDataRange(),entity.getCreateUserName(),entity.getType());
    }

    @Override
    public Map<String, Object> uploadRecords(RecordPageParam param, Integer dataRange, String region) {
        StringBuilder sql = new StringBuilder("select * from fxqh_ps_hazard_data_record where data_range = "+dataRange);
        if(PlatformObjectUtils.isNotEmpty(param.getUserName())){
            sql.append(" and create_user_name like '%"+param.getUserName()+"%'");
        }
        if(StrUtil.isBlankIfStr(region)){
            sql.append(" and region like '%"+region+"%'");
        }
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        int firstIndex = (param.getPage() - 1) * param.getSize();
        int lastIndex = param.getSize();
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        sql.append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(RecordsVo.class)).size();
        List<RecordsVo> samplingData = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(RecordsVo.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", samplingData);
        return map;
    }
}
